//---------------------------------------------------------------------------- Package
const _ = require('lodash')
//---------------------------------------------------------------------------- Config
const { MYSQL } = require('../../config')
//---------------------------------------------------------------------------- Plugin
const mysql = require('../../plugin/util/mysql')
const time = require('../../plugin/util/time')
const sheet = require('../../plugin/util/sheet')
const md5 = require('../../plugin/md5/md5')
const nodeExcel = require('excel-export')
const xlsx = require('node-xlsx');

// ---------------------------------------------------------------------------- GET
exports.get = {


  /**
   * 导出职位信息
   */
  '/get/job/info/excel':async(ctx,next) =>{
    let info = await mysql.query(MYSQL.XIONGAN,['select * from job'],[])
    let conf={};
    conf.cols = [
      {caption:'职位名称',type:'string'},
      {caption:'公司名称',type:'string'},
      {caption:'公司id',type:'number'},
      {caption:'职位id',type:'number'},
      {caption:'薪资',type:'string'},
      {caption:'区域',type:'string'},
      {caption:'福利待遇',type:'string'},
      {caption:'学历',type:'string'},
      {caption:'工作经验',type:'string'},
      {caption:'工作地址',type:'string'},
      {caption:'职位要求',type:'string'},


    ];
    conf.rows = []
    for(let i of info[0]){
      conf.rows.push([i.name,i.cname,i.cid,i.jtid,i.pay,i.area,i.benefit,i.education,i.experience,i.address,i.statement])
    }
    let xls_data = nodeExcel.execute(conf);
    let data = new Buffer(xls_data,'binary')
    ctx.set('Content-Type', 'application/vnd.openxmlformats');
    ctx.set("Content-Disposition", "attachment; filename=" + "job.xlsx");
    ctx.body = data
    return
  },
  /**
   * 一键投递求职者信息excel
   */
  '/get/excel/jobseeker/info':async(ctx,next) =>{
    let sql = 'select * from jobseekerinfo order by  time_create desc'
    let arg = []
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    for(let i of info[0]){
      i.time_create = new Date(i.time_create*1000).format('yyyy-MM-dd hh:mm:ss')
      i.time_check = new Date(i.time_check*1000).format('yyyy-MM-dd hh:mm:ss')
      if(i.islink == 1){
        i.islink = '是'
      }else{
        i.islink = '否'
      }
    }
    let conf={};

    //姓名,电话,性别,年龄,求职意向,工作所在地,备注

    // conf.cols = [
    //   {caption:'姓名',type:'string'},
    //   {caption:'电话',type:'string'},
    //   {caption:'发布时间',type:'string'},
    //   {caption:'反馈时间',type:'string'},
    //   {caption:'备注',type:'string'},
    //   {caption:'是否反馈',type:'string'},
    // ];

    conf.cols = [
      {caption:'姓名',type:'string'},
      {caption:'电话',type:'string'},
      {caption:'性别',type:'string'},
      {caption:'年龄',type:'string'},
      {caption:'求职意向',type:'string'},
      {caption:'工作所在地',type:'string'},
      {caption:'备注',type:'string'},
    ];


//{'sex':'男', 'age':'111111111111', 'intention':'1111111111111', 'area':'1111111'}
    conf.rows = []
    for(let i of info[0]){
      var object = {};
      if(i['object'] == "" || i['object'] == null || !i['object']){
        object = {'sex':'', 'age':'', 'intention':'', 'area':''};
      }else{
        object = i.object.replace(/"""/g,'""');
        object = object.replace(/'/g,'"');
        //console.log(object)

        object = JSON.parse(object);


      }


      var sex = object['sex'];
      var age = object['age'];
      var intention = object['intention'];
      var area = object['area'];

      conf.rows.push([i.username,i.mobile,sex,age,intention,area,i.remark]);

    }

    let xls_data = nodeExcel.execute(conf);
    let data = new Buffer(xls_data,'binary')
    ctx.set('Content-Type', 'application/vnd.openxmlformats');
    ctx.set("Content-Disposition", "attachment; filename=" + "jobseekers.csv");
    ctx.body = data
    return
  },


  /**
   * 村镇递求职者信息excel
   */
  '/get/excel/village/info':async(ctx,next) =>{
    let sql = 'select * from reg_village_person order by  time_create desc'
    let arg = []
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    for(let i of info[0]){
      i.time_create = new Date(i.time_create*1000).format('yyyy-MM-dd hh:mm:ss')
      i.hascert = i.hascert ? "是" : "否";
      i.haswork = i.haswork ? "已就业" : "未就业";
      i.train_intention = i.hascert ? "是" : "否";
    }
    let conf={};

    conf.cols = [
      {caption:'区县',type:'string'},
      {caption:'镇',type:'string'},
      {caption:'村',type:'string'},
      {caption:'姓名',type:'string'},
      {caption:'电话',type:'string'},
      {caption:'性别',type:'string'},
      {caption:'身份证号',type:'string'},
      {caption:'年龄',type:'string'},
      {caption:'学历',type:'string'},
      {caption:'具备技能',type:'string'},
      {caption:'是否有专业证书',type:'string'},
      {caption:'求职意向',type:'string'},
      {caption:'就业状态',type:'string'},
      {caption:'职业培训意向',type:'string'},
    ];

    conf.rows = []
    for(let i of info[0]){
      conf.rows.push([
        i.area,
        i.town,
        i.village,
        i.name,
        i.phone,
        i.sex,
        i.pincode,
        i.age,
        i.education,
        i.skill,
        i.hascert,
        i.intention,
        i.haswork,
        i.train_intention,
        i.time_create
        ])
    }

    let xls_data = nodeExcel.execute(conf);
    let data = new Buffer(xls_data,'binary')
    ctx.set('Content-Type', 'application/vnd.openxmlformats');
    ctx.set("Content-Disposition", "attachment; filename=" + "reg_village_person.csv");
    ctx.body = data
    return
  },

  /**
   * 村镇递求职者信息excel
   */
  '/get/excel/changcheng/info':async(ctx,next) =>{
    let sql = 'select * from changcheng order by  time_create desc'
    let arg = []
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    let conf={};

    conf.cols = [
      {caption:'意向地区',type:'string'},
      {caption:'姓名',type:'string'},
      {caption:'性别',type:'string'},
      {caption:'政治面貌',type:'string'},
      {caption:'婚姻状况',type:'string'},
      {caption:'民族',type:'string'},
      {caption:'籍贯',type:'string'},
      {caption:'户口所在地',type:'string'},
      {caption:'家庭电话',type:'string'},
      {caption:'手机号码',type:'string'},
      {caption:'身份证号',type:'string'},
      {caption:'学历',type:'string'},
      {caption:'毕业时间',type:'string'},
      {caption:'毕业学校',type:'string'},
      {caption:'专业',type:'string'},
      {caption:'备注',type:'string'},
    ];

    conf.rows = []
    for(let i of info[0]){
      conf.rows.push([
        i.address_w,
        i.name,
        i.sex,
        i.zzmm,
        i.hyzk,
        i.minzu,
        i.jiguan,
        i.address_h,
        i.tel,
        i.phone,
        i.number,
        i.education,
        i.overtime,
        i.school,
        i.major,
        i.beizhu,
      ])
    }

    let xls_data = nodeExcel.execute(conf);
    let data = new Buffer(xls_data,'binary')
    ctx.set('Content-Type', 'application/vnd.openxmlformats');
    ctx.set("Content-Disposition", "attachment; filename=" + "changcheng.csv");
    ctx.body = data
    return
  },




   /**
   * 一键招人才公司信息excel
   */
  '/get/excel/companys/info':async(ctx,next)=>{
    let sql = 'select * from companys order by  time_create desc'
    let arg = []
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    for(let i of info[0]){
      i.time_create = new Date(i.time_create*1000).format('yyyy-MM-dd hh:mm:ss')
      i.time_check = new Date(i.time_check*1000).format('yyyy-MM-dd hh:mm:ss')
      if(i.islink == 1){
        i.islink = '是'
      }else{
        i.islink = '否'
      }
    }
    let conf={};
    conf.cols = [
      {caption:'公司名称',type:'string'},
      {caption:'联系人',type:'string'},
      {caption:'联系电话',type:'string'},
      {caption:'发布时间',type:'string'},
      {caption:'反馈时间',type:'string'},
      {caption:'备注',type:'string'},
      {caption:'是否反馈',type:'string'},
    ];
    conf.rows = []
    for(let i of info[0]){
      conf.rows.push([i.cname,i.contact_name,i.contact_mobile,i.time_create,i.time_check,i.remark,i.islink])
    }
    let xls_data = nodeExcel.execute(conf);
    let data = new Buffer(xls_data,'binary')
    ctx.set('Content-Type', 'application/vnd.openxmlformats');
    ctx.set("Content-Disposition", "attachment; filename=" + "companys.csv");
    ctx.body = data
    return
  },

  /**
   * 一键投递求职者信息
   */
  '/get/jobseeker/info':async(ctx,next) =>{
    let page = ctx.query.page
    let start_time = ctx.query.start_time
    let end_time = ctx.query.end_time
    let where_sql = time_sql_where(start_time, end_time);
    if(_.isUndefined(page)){
      page=1
    }
    page = (page < 1) ? 0 : (page-1)
    let num = 15

    if(where_sql.indexOf("where") == -1){
      where_sql = " where id>0"+where_sql;
    }

    if(ctx.query['username']){
      where_sql = where_sql+' and username like"%'+ctx.query['username']+'%"';
    }

    if(ctx.query['mobile']){
      where_sql = where_sql+' and mobile like"%'+ctx.query['mobile']+'%"';
    }

    var object_s_arr = [
    "sex",
    "age",
    "intention",
    "area",
    ];

    for(var item of object_s_arr){
      if(ctx.query[item] && ctx.query[item] != ""){
        where_sql = where_sql+' and object like"%'+ctx.query[item]+'%"';
      }
    }


    let sql = 'select * from jobseekerinfo '+where_sql+' order by  time_create desc  limit ?,?'
    let arg = [parseInt(page) * num,num]
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = info[0]
    ctx.body = sheet[0]
    return 
  },

  /**
   * 村镇求职者信息
   */
  '/get/village/info':async(ctx,next) =>{
    let page = ctx.query.page
    let start_time = ctx.query.start_time
    let end_time = ctx.query.end_time
    let where_sql = time_sql_where(start_time, end_time);

    let search_val = [
        "area",
        "town",
        "village",
        "name",
        "phone",
        "sex",
        "pincode",
        "age",
        "education",
        "skill",
        "hascert",
        "intention",
        "haswork",
        "train_intention",
    ]

    for(var index of search_val){
      if(ctx.query[index] != "" && ctx.query[index]){
        where_sql = where_sql + ' and '+index+' like "%'+ctx.query[index]+'%"';
      }
    }

    if(where_sql.indexOf("where") == -1){
      where_sql = " where id>0"+where_sql;
    }

    console.log(where_sql)

    if(_.isUndefined(page)){
      page=1
    }
    page = (page < 1) ? 0 : (page-1)
    let num = 15
    let sql = 'select * from reg_village_person '+where_sql+' order by  time_create desc  limit ?,?'
    let arg = [parseInt(page) * num,num]
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = info[0]
    ctx.body = sheet[0]
    return 
  },


  /**
   * 长城体检表信息列表
   */
  '/get/changcheng/info':async(ctx,next) =>{
    let page = ctx.query.page
    let start_time = ctx.query.start_time
    let end_time = ctx.query.end_time
    let where_sql = time_sql_where(start_time, end_time);

    let search_val = [
      "address_w",
      "name",
      "sex",
      "zzmm",
      "hyzk",
      "minzu",
      "jiguan",
      "address_h",
      "tel",
      "phone",
      "number",
      "education",
      "overtime",
      "school",
      "major",
      "beizhu",
    ]

    for(var index of search_val){
      if(ctx.query[index] != "" && ctx.query[index]){
        where_sql = where_sql + ' and '+index+' like "%'+ctx.query[index]+'%"';
      }
    }

    if(where_sql.indexOf("where") == -1){
      where_sql = " where id>0"+where_sql;
    }

    if(_.isUndefined(page)){
      page=1
    }
    page = (page < 1) ? 0 : (page-1)
    let num = 15
    let sql = 'select * from changcheng '+where_sql+' order by  time_create desc  limit ?,?'
    let arg = [parseInt(page) * num,num]
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = info[0]
    ctx.body = sheet[0]
    return 
  },


  /**
   * 村镇求职者信息详情
   */
  '/get_village/datail/:id':async(ctx,next) => {
    let id = ctx.params.id
    let data = await mysql.query(MYSQL.XIONGAN,['select * from reg_village_person where id=?'],[id])
    sheet[0].data = data
    ctx.body =sheet[0]
  },

  /**
   * 长城求职者信息详情
   */
  '/get_changcheng/datail/:id':async(ctx,next) => {
    let id = ctx.params.id
    let data = await mysql.query(MYSQL.XIONGAN,['select * from changcheng where id=?'],[id])
    sheet[0].data = data
    ctx.body =sheet[0]
  },



  /**
   * 一键招人才公司信息
   */
  '/get/companys/info':async(ctx,next)=>{
    let {page} = ctx.query
    if(_.isUndefined(page)){
      page=1
    }
    page = (page < 1) ? 0 : (page-1)
    let num = 15
    let sql = 'select * from companys order by  time_create desc  limit ?,?'
    let arg = [parseInt(page) * num,num]
    let info = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = info[0]
    ctx.body = sheet[0]
    return 
  },



  /**
   * 获取各种列表的总条数(get参数区分)
   */
  '/get/jobseeker/count':async(ctx,next) =>{
    let sql = ''
    let type = ctx.query.type
    let start_time = ctx.query.start_time
    let end_time = ctx.query.end_time
    let where_sql = time_sql_where(start_time, end_time);
    if(type == "jobseekerinfo"){
      sql = 'select count(id) as countnum from jobseekerinfo'
    }else if(type == "companys"){
      sql = 'select count(id) as countnum from companys'
    }else if(type == "reg_village_person"){
      sql = 'select count(id) as countnum from reg_village_person'
    }else if(type == "changcheng"){
      sql = 'select count(id) as countnum from changcheng'
    }
    sql = sql+" "+where_sql;
    console.log(sql)
    let info = await mysql.query(MYSQL.XIONGAN,[sql],[])
    sheet[0].data = info[0]
    ctx.body = sheet[0]
    return 
  },



     /**
   * 审核通过公司列表
   */
  '/get_company/list': async(ctx,next) => {
    let {cname,address,page} = ctx.query
    if(_.isUndefined(page)){
      page = 1
    }
    let sql = 'select A.*,B.legalPerson,B.intro,B.logo,B.license from company as A,companyinfo as B '
    let where = ''
    let arg = []
    let num = 10
    if(cname){
      where += (where == '') ? ' where A.cname like "%' + cname + '%"' : ' and A.cname like "%' + cname +'%"'
    }
    if(address){
      where += (where == '') ? ' where A.address like "%' + address + '%"' : '  and A.address like "%' + address +'%"'
    }
    where += (where == '') ? ' where A.status = 1' : ' and A.status =1'

    if(page != 'all'){
      page = (page <1)? 0 : (page-1)
      sql = sql + where + ' and A.id = B.cid order by id desc '  + ' limit ?,?'
      console.log('sql==',sql)
      arg.push((parseInt(page) * num ),num)
    }else{
      sql = sql + where + ' and A.id = B.cid  order by id desc ';
    }

    let data = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = data
    ctx.body =sheet[0]
  },
  /**
   * 公司列表详情
   */
  '/get_company/datail/:id':async(ctx,next) => {
    let id = ctx.params.id
    let data = await mysql.query(MYSQL.XIONGAN,['select A.*,B.legalPerson,B.intro,B.logo,B.license from company as A,companyinfo as B where A.id=? and A.id = B.cid '],[id])
    sheet[0].data = data
    ctx.body =sheet[0]
  },
    /**
   * 待审核公司列表
   */
  '/get_company/examine': async(ctx,next) => {
    let {cname,address,page} = ctx.query
    if(_.isUndefined(page)){
      page = 1
    }
    let sql = 'select A.*,B.legalPerson,B.intro,B.logo,B.license from company as A,companyinfo as B '
    let where = ''
    let arg = []
    let num = 10
    if(cname){
      where += (where == '') ? 'where A.cname like "%' + cname + '%"' : ' and A.cname like "%' + cname +'%"'
    }
    if(address){
      where += (where == '') ? 'where  A.address like "%' + address + '%"' : '  and A.address like "%' + address +'%"'
    }
    where += (where == '') ? 'where A.status = 0' : ' and A.status = 0'
    page = (page <1)? 0 : (page-1)
    sql = sql + where +  ' and A.id=B.cid' +' limit ?,?'
    console.log('sql==',sql)
    arg.push((parseInt(page) * num ),num)
    let data = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = data
    ctx.body =sheet[0]
  },

    /**
   * 公司列表详情的发布岗位
   */
  '/get_job/datail/:id':async(ctx,next) => {
    let id = ctx.params.id
    let data = await mysql.query(MYSQL.XIONGAN,['select B.*,A.cname from company as A,job as B where A.id =? and B.cid= A.id'],[id])
    sheet[0].data = data
    ctx.body =sheet[0]
  },

  /**
   * 公司未通过审核列表
   */
  '/get_company/no': async(ctx,next) => {
    let {cname,address,page} = ctx.query
    if(_.isUndefined(page)){
      page = 1
    }
    let sql = 'select A.*,B.legalPerson,B.intro,B.logo,B.license from company as A,companyinfo as B '
    let where = ''
    let arg = []
    let num = 10
    if(cname){
      where += (where == '') ? 'where A.cname like "%' + cname + '%"' : ' and A.cname like "%' + cname +'%"'
    }
    if(address){
      where += (where == '') ? 'where A.address like "%' + address + '%"' : '  and A.address like "%' + address +'%"'
    }
    where += (where == '') ? 'where A.status = 2' : ' and A.status = 2'
    page = (page <1)? 0 : (page-1)
    sql = sql + where +' and A.id=B.cid'  +' limit ?,?'
    arg.push((parseInt(page) * num ),num)
    let data = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = data
    ctx.body =sheet[0]
  },
  
  /**
   * 公司详情
   */
  '/company/detail/:id':async(ctx,next) => {
    let id = ctx.params.id
    let data = await mysql.query(MYSQL.XIONGAN,['select * from company as A,companyinfo as B where A.status_check = 0 and A.id=? and A.id = B.cid'],[id])
    sheet[0].data = data
    ctx.body =sheet[0]
  },
  /**
   * 岗位列表
   */
  '/job/list':async(ctx,next) => {
    let {examine,name,page} = ctx.query
    if(_.isUndefined(page)){
      page = 1
    }
    let num =15
    let sql = 'select A.*,B.cname from job as A,company as B'
    let arg = []
    let where = ''
    if(name){
      where += (where == '')? ' where A.jobname like "%' + name + '%"' : ' and A.jobname like "%' + name + '%"'
    }
    where += (where == '')?' where A.status_check=? and A.status_job=1':' and A.status_check=? and A.status_job=1'
    arg.push(examine)
    sql  = sql + where + ' and A.cid = B.id  order by id desc' + ' limit ?,?'
    page = (page < 1) ? 0 :(parseInt(page)-1)
    arg.push(parseInt(page)* parseInt(num),num)
    let data = await mysql.query(MYSQL.XIONGAN,[sql], arg)
    sheet[0].data = data[0]
    ctx.body = sheet[0]
  },
    /**
   * 岗位详情
   */
  '/job/detail/:id':async(ctx,next) => {
    let id = ctx.params.id
    let job = await mysql.query(MYSQL.XIONGAN,['select A.*,B.cname from job as A,company as B where A.cid=B.id and A.id = ?'],[id])
    sheet[0].data = job[0][0]
    ctx.body = sheet[0]
  },
   /**
   * 区域列表
   */
  '/area/list': async (ctx, next) => {
    let area = await mysql.query(MYSQL.XIONGAN, ['select * from area'], [null])
    sheet[0].data = area[0]
    ctx.body = sheet[0]
  },
  /**
   * 经验要求列表
   */
  '/experience/list': async (ctx, next) => {
    sheet[0].data =['不限','无经验','1年以下','1-3年','3-5年','5-10年','10年以上']
    ctx.body = sheet[0]
  },
  /**
   * 薪资列表
   */
  '/pay/list': async (ctx, next) => {
    sheet[0].data = ['2000以下','2000-3000','3000-5000','5000-10000','10000以上','面议']
    ctx.body = sheet[0]
  },
  /**
   * 福利待遇列表
   */
  '/benefit/list': async (ctx, next) => {
    sheet[0].data = [{name:'五险一金',checked:false},{name:'年底双薪',checked:false},{name:'绩效奖金',checked:false},{name:'年终分红',checked:false},{name:'股票期权',checked:false},{name:'加班补助',checked:false},{name:'全勤奖',checked:false},{name:'包吃包住',checked:false},{name:'交通补助',checked:false},{name:'餐补',checked:false},{name:'房补',checked:false},{name:'通讯补贴',checked:false},{name:'采暖补贴',checked:false},{name:'带薪年假',checked:false},{name:'弹性工作',checked:false},{name:'补充医疗保险',checked:false},{name:'定期体检',checked:false},{name:'免费班车',checked:false},{name:'员工旅游',checked:false},{name:'高温补贴',checked:false},{name:'节日福利',checked:false}]
    ctx.body = sheet[0]
  },
   /**
   * 学历要求列表
   */
  '/education/list': async (ctx, next) => {
    sheet[0].data = ['不限','初中','中技','高中','中专','大专','本科','硕士','MBA','EMBA','博士','其他']
    ctx.body = sheet[0]
  },
  /**
   * 
   */
  '/company/scope': async (ctx, next) => {
    sheet[0].data = ['20人以下','20-99人','100-499人','500-999人','1000-9999人','10000人以上']
    ctx.body = sheet[0]
  },
   /**
   * 职位类型列表
   */
  '/jobtype/list': async (ctx, next) => {
    let data = await mysql.query(MYSQL.XIONGAN, ['select * from job_type'], [null])
    sheet[0].data = data[0]
    ctx.body = sheet[0]
  },


  //删除公司
  '/get/delete/company/:id':async(ctx,next) =>{
    let id = ctx.params.id
    let job = await mysql.query(MYSQL.XIONGAN, ['select * from job where cid=?'], [id])
    let job_data = job[0];
    if(job_data.length == 0){
      await mysql.query(MYSQL.XIONGAN, ['delete from companyinfo where cid=?'], [id])
      await mysql.query(MYSQL.XIONGAN, ['delete from companymsg where cid=?'], [id])
      await mysql.query(MYSQL.XIONGAN, ['delete from companymsg where cid=?'], [id])
      mysql.query(MYSQL.XIONGAN, ['delete from company where id=?'], [id])
    }
  },


  //删除城镇找工作数据
  '/get/delete/village/:id':async(ctx,next) =>{
    let id = ctx.params.id
    mysql.query(MYSQL.XIONGAN, ['delete from reg_village_person where id=?'], [id]);
  },

  //删除长城找工作数据
  '/get/delete/changcheng/:id':async(ctx,next) =>{
    let id = ctx.params.id
    mysql.query(MYSQL.XIONGAN, ['delete from changcheng where id=?'], [id]);
  },


}
// ---------------------------------------------------------------------------- POST
exports.post = {
/**
   * 发布岗位
   */
  '/job/add': async (ctx, next) => {
    let time = Date.parse(new Date()).toString().slice(0,10)
    let { cid, cname, type,name, jtid, pay, area,benefit,education,experience,address,statement,requirements } = ctx.request.fields
    let check = await mysql.query(MYSQL.XIONGAN,['select * from job where cid=? and jtid=?'],[cid,jtid])

    let jobName = await mysql.query(MYSQL.XIONGAN,['select name from job_type where id=?'],[jtid])
    let sql = 'insert into job(cid,jobname,jtid,pay,area,time_create,benefit,education,experience,address,statement,ishot,status_job,status_check) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
    let arg = [cid,jobName[0][0].name,jtid,pay,area,time,benefit,education,experience,address,statement,0,1,0]
    let data = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = data[0]
    sheet[0].message = '发布成功'
    ctx.body = sheet[0]

    //禁止发布相同岗位
    // if(_.isEmpty(check[0])){
    //   let jobName = await mysql.query(MYSQL.XIONGAN,['select name from job_type where id=?'],[jtid])
    //   let sql = 'insert into job(cid,jobname,jtid,pay,area,time_create,benefit,education,experience,address,statement,ishot,status_job,status_check) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
    //   let arg = [cid,jobName[0][0].name,jtid,pay,area,time,benefit,education,experience,address,statement,0,1,0]
    //   let data = await mysql.query(MYSQL.XIONGAN,[sql],arg)
    //   sheet[0].data = data[0]
    //   sheet[0].message = '发布成功'
    //   ctx.body = sheet[0]
    // }else{
    //   sheet[10004].message = '您已发布该岗位'
    //   sheet[10004].data = []
    //   ctx.body = sheet[10004]
    // }
  },


  //导入excel到公司
  '/post/excelimport':async(ctx,next) =>{
      let filepath = ctx.request.files[0].path;
      var obj = xlsx.parse(filepath);
      var import_data = obj[0].data;
      var type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
      if(ctx.request.files[0].type != type){
        ctx.body = {}
        return
      }
      for (var key in import_data) {
        if(key == 0){
          continue;
        }else{

          var username = '';//用户名
          var password = '';//密码

          var status = 1;//审核通过
          var ishot = 0;//审核通过
          var nature = '';//企业类型
          var trade = 66;//经营范围

          var cname = import_data[key][0];//公司名称
          var email = import_data[key][1];//邮箱
          var contact_name = import_data[key][2];//联系人
          var contact_mobile = import_data[key][3];//联系人电话
          var address = import_data[key][5];//公司地址



          var legalPerson = import_data[key][2];//法人
          var intro = import_data[key][4];//公司简介

          var license = '';//营业执照
          var logo = '';//logo


          let sql = 'insert into company(email,username,password,contact_name,contact_mobile,ishot,status,cname,nature,trade,address) values(?,?,?,?,?,?,?,?,?,?,?)';
          let data = await mysql.query(MYSQL.XIONGAN,[sql],[
          email,
          username,
          password,
          contact_name,
          contact_mobile,
          ishot,
          status,
          cname,
          nature,
          trade,
          address
          ]);

          var cid = data[0].insertId;
          if(cid){
            let sql2 = 'insert into companyinfo(cid,legalPerson,intro,license,logo) values(?,?,?,?,?)';
            await mysql.query(MYSQL.XIONGAN,[sql2],[
            cid,
            legalPerson,
            intro,
            license,
            logo
            ]); 
          }else{
            break;
            ctx.body = {}
            return  
          }
        }
      }
      sheet[0].data = [];
      ctx.body = sheet[0];
      return  
  },



}
// ---------------------------------------------------------------------------- PUT
exports.put = {
    /**
   * 公司审核
   */
  '/company/examine':async(ctx,next) => {
    let {id,examine,name} = ctx.request.fields
    let content = []
    let myDate  =new Date().toLocaleDateString();
    let data = await await mysql.query(MYSQL.XIONGAN,['update company set status = ? where id = ?'],[examine,id])
    let company = await mysql.query(MYSQL.XIONGAN,['select * from company where id=?'],[id])
    if(examine == 1){
       content = '您申请的'+name+'企业认证于'+myDate+'，审核通过，请注意查看'
    }else{
      content = '您申请的'+name+'企业认证于'+myDate+'，审核未通过，请注意查看'
    }
    let tmp = Date.parse( new Date() ).toString().substr(0,10);
    let sql = 'insert into companymsg(cid,content,isread,reason,type,time) values(?,?,?,?,?,?)'
    let arg = [id,content,0,'',0,tmp]
    await await mysql.query(MYSQL.XIONGAN, [sql],arg)
    sheet[0].data = data
    sheet[0].message='审核成功'
    ctx.body =sheet[0]
  },
  /**
   * 职位编辑
   */
  '/edit/job': async (ctx, next) => {
    let { jobname,pay,area,benefit,education,experience,address,statement,requirements, status_check, id, cid } = ctx.request.fields
    console.log(cid);
    let time = Date.parse(new Date()).toString().substr(0,10)
    let data = await mysql.query(MYSQL.XIONGAN, ['update job set jobname = ?,pay=?,area=?,benefit=?, education=?,experience =?,address = ?, statement = ?,status_check=?,time_create=?,cid=? where id =?'],[jobname,pay, area,benefit,education,experience,address,statement,status_check,time,cid,id])
    sheet[0].data = data
    sheet[0].message='编辑成功'
    ctx.body = sheet[0]
  },
  /**
   * 设为热门职位
   */
  '/hot/job': async (ctx, next) => {
    let {  ishome,ishot, id, sort} = ctx.request.fields
    let list = await mysql.query(MYSQL.XIONGAN,['select * from job where ishome = 1'],[null])
    let len = list[0].length
    console.log(len)
    // if(len >= 12){
    //   sheet[10004].message = '已达到上限,不能执行此操作'
    //   ctx.body = sheet[10004]
    // }else{
      let data = await mysql.query(MYSQL.XIONGAN, ['update job set ishot = ?,ishome=?,sort=? where id =?'],[ishot,ishome,sort,id])
      sheet[0].data = data
      sheet[0].message='更新成功'
      ctx.body = sheet[0]
    // }
    
  },
  /**
   * 是否在首页显示
   */
  '/home/job': async (ctx, next) => {
    let {home,id} = ctx.request.fields
    let list = await mysql.query(MYSQL.XIONGAN,['select * from job where home = 1'],[null])
    let len = list[0].length
    if(home==true){
      if(len >= 11 ){
        sheet[10004].message = '已达到上限,不能执行此操作'
        ctx.body = sheet[10004]
      }else{
        let data = await mysql.query(MYSQL.XIONGAN,['update job set home = ? where id=?'],[home,id])
        sheet[0].data = data
        sheet[0].message = "更新成功"
        ctx.body = sheet[0]
      }
    }else{
      let data = await mysql.query(MYSQL.XIONGAN,['update job set home = ? where id=?'],[home,id])
      sheet[0].data = data
      sheet[0].message = "更新成功"
      ctx.body = sheet[0]
    }
    
  },
  /**
   * 删除岗位 
   */
  '/delete/job': async (ctx, next) =>{
    let { id } = ctx.request.fields
    console.log(ctx.request.fields)
    let data = await mysql.query(MYSQL.XIONGAN,['update job set status_job=2 where id = ?'],[id])
    sheet[0].data = data
    ctx.body = sheet[0]
  },
  /**
   * 设为热门公司
   */
  '/hot/compant': async (ctx, next) => {
    let { ishot, id } = ctx.request.fields
    let data = await mysql.query(MYSQL.XIONGAN, ['update company set ishot = ? where id =?'],[ishot, id])
    sheet[0].data = data
    sheet[0].message='更新成功'
    ctx.body = sheet[0]
  },

    /**
   * 岗位审核
   */
  '/job/examine':async(ctx,next) => {
    let {id,examine,reason} = ctx.request.fields
    let time = _.now().toString().substr(0,10)
    let content = ''
    let job = await mysql.query(MYSQL.XIONGAN,['update job set status_check = ? where id=?'],[examine,id])
    let data = await mysql.query(MYSQL.XIONGAN,['select A.*,B.id,B.cname from job A,company B where A.id=? and A.cid = B.id '],[id])
    if(_.isUndefined(reason)){
      reason = ''
    }
    console.log('examine==',examine)
    if(examine == 1){
      content = '您申请的'+data[0][0].cname+'岗位认证于'+ new Date().format('yyyy-MM-dd') +'，审核通过，请注意查看'
    }else{
      content = '您申请的'+data[0][0].cname+'岗位认证于'+ new Date().format('yyyy-MM-dd')+'，审核未通过，原因为'+ reason +'，请注意查看'
    }

    let sql = 'insert into companymsg(cid,content,isread,reason,type,time) values(?,?,?,?,?,?)'
    let arg = [data[0][0].cid,content,0,reason,1,time]
    console.log('sql==',sql)
    console.log('arg==',arg)
    await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = job
    sheet[0].message='审核成功'
    ctx.body = sheet[0]
  },
   /**
   * 岗位审核
   */
  '/job/exdit':async(ctx,next) => {
    let {id,name,type,pay,area,benefit,education,experience,reason} = ctx.request.fields
    let time = _.now().toString().substr(0,10)
    let content = []
    let job = await mysql.query(MYSQL.XIONGAN,['update job set status_check = ? where id=?'],[examine,id])
    let data = await mysql.query(MYSQL.XIONGAN,['select A.*,B.id from job A,company B where A.id=? and A.cid = B.id '],[id])
    if(data[0].examine == 1){
      content = '您申请的'+data[0][0].cname+'岗位认证于'+ new Date().format('yyyy-MM-dd') +'，审核通过，请注意查看'
    }else{
      content = '您申请的'+data[0][0].cname+'岗位认证于'+new Date().format('yyyy-MM-dd')+'，审核未通过，原因为'+ reason +'，请注意查看'
    }
    let sql = 'insert into companymsg(cid,content,isread,reason,type,time) values(?,?,?,?,?,?)'
    let arg = [data[0][0].cid,content,0,reason,1,time]
    await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = job
    sheet[0].message='审核成功'
    ctx.body = sheet[0]
  },

  /**
   * [添加公司]
   */
  '/company/add': async (ctx, next) =>{
    let companyinfo_filed = ['legalPerson','intro','logo','license'];

    let company_set_arr = [];
    let company_val_arr = [];

    let companyinfo_set_arr = [];
    let companyinfo_val_arr = [];

    let company_placeholder_arr = [];
    let companyinfo_placeholder_arr = [];

    let fields = ctx.request.fields
    for (var key in ctx.request.fields) {
      if(companyinfo_filed.indexOf(key) == -1){
        company_set_arr.push(key);
        if(key == 'password'){
          company_val_arr.push(md5(fields[key]));
        }else{
          company_val_arr.push(fields[key]);
        }
        company_placeholder_arr.push('?');
      }else{
        companyinfo_set_arr.push(key);
        companyinfo_val_arr.push(fields[key]);
        companyinfo_placeholder_arr.push('?');
      }
    }

    let data = await mysql.query(MYSQL.XIONGAN,['insert into company('+company_set_arr.join(',')+') values('+company_placeholder_arr.join(',')+')'],company_val_arr);

    companyinfo_set_arr.push('cid');
    companyinfo_val_arr.push(data[0].insertId);
    companyinfo_placeholder_arr.push('?');

    await mysql.query(MYSQL.XIONGAN,['insert into companyinfo('+companyinfo_set_arr.join(',')+') value('+companyinfo_placeholder_arr.join(',')+')'],companyinfo_val_arr);

    sheet[0].data = data
    ctx.body = sheet[0]
  },


  /**
   * 修改公司资料
   */
  '/company/change': async (ctx, next) =>{

    // let { name, address, email, contact_name, contact_mobile, summary, certificate, logo, contract, id } = ctx.request.fields
    // let data = await mysql.query(MYSQL.XIONGAN,['update company set cname=?, address=?, email=?, contact_name=?, contact_mobile=? where id=?'],[name, address,email, contact_name, contact_mobile, id])
    // await mysql.query(MYSQL.XIONGAN,['update companyinfo set intro=?,license=?,logo=? where cid=?'],[summary,certificate,logo,id])

    let companyinfo_filed = ['legalPerson','intro','logo','license'];

    let company_set_arr = [];
    let company_val_arr = [];

    let companyinfo_set_arr = [];
    let companyinfo_val_arr = [];

    let fields = ctx.request.fields
    for (var key in ctx.request.fields) {
      if(key == 'id'){
        continue;
      }
      if(companyinfo_filed.indexOf(key) == -1){
        company_set_arr.push(key+"=?");
        if(key == 'password'){
          company_val_arr.push(md5(fields[key]));
        }else{
          company_val_arr.push(fields[key]);
        }
      }else{
        companyinfo_set_arr.push(key+"=?");
        companyinfo_val_arr.push(fields[key]);
      }
    }

    company_val_arr.push(fields['id']);
    companyinfo_val_arr.push(fields['id']);


    let data = await mysql.query(MYSQL.XIONGAN,['update company set '+company_set_arr.join(',')+' where id=?'],company_val_arr)
    await mysql.query(MYSQL.XIONGAN,['update companyinfo set '+companyinfo_set_arr.join(',')+' where cid=?'],companyinfo_val_arr)

    sheet[0].data = data
    ctx.body = sheet[0]

  },


   /**
   * 修改城镇找工作资料
   */ 
  '/village/change': async (ctx, next) =>{
    let village_set_arr = [];
    let village_val_arr = [];
    let fields = ctx.request.fields;

    for(var key in fields){
      if(key == 'id'){
        continue;
      }
      village_set_arr.push(key+"=?");
      village_val_arr.push(fields[key]);
    }

    village_val_arr.push(fields['id']);

    let data = await mysql.query(MYSQL.XIONGAN,['update reg_village_person set '+village_set_arr.join(',')+' where id=?'],village_val_arr)
    
    sheet[0].data = data
    ctx.body = sheet[0]

  },
   /**
   * 修改长城资料
   */ 
  '/changcheng/change': async (ctx, next) =>{
    let village_set_arr = [];
    let village_val_arr = [];
    let fields = ctx.request.fields;

    for(var key in fields){
      if(key == 'id'){
        continue;
      }
      village_set_arr.push(key+"=?");
      village_val_arr.push(fields[key]);
    }

    village_val_arr.push(fields['id']);

    let data = await mysql.query(MYSQL.XIONGAN,['update changcheng set '+village_set_arr.join(',')+' where id=?'],village_val_arr)
    
    sheet[0].data = data
    ctx.body = sheet[0]

  },



  /**
   * 更新求职者信息
   */
  '/update/jobseekerinfo':async (ctx,next) =>{
    let {id,islink,remark} = ctx.request.fields
    let time = Date.parse(new Date()).toString().substr(0,10)
    let sql = 'update jobseekerinfo set islink=?,time_check=?,remark=? where id=?'
    let arg = [islink,time,remark,id]
    await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = []
    ctx.body = sheet[0]
  },
  /**
   * 更新企业找人才信息
   */
  '/update/companys/info':async(ctx,next)=>{
    let {id,islink,remark,bindworker} = ctx.request.fields
    let time = Date.parse(new Date()).toString().substr(0,10)
    let sql = 'update companys set islink=?,time_check=?,remark=?,bindworker=? where id=?'
    let arg = [islink,time,remark,bindworker,id]
    await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = []
    ctx.body = sheet[0]
  },

  /**
   * 更新企业找人才信息company  新的
   */
  '/update/company/info':async(ctx,next)=>{
    let {id,islink,remark,bindworker} = ctx.request.fields
    let time = Date.parse(new Date()).toString().substr(0,10)
    let sql = 'update company set islink=?,time_check=?,remark=?,bindworker=? where id=?'
    let arg = [islink,time,remark,bindworker,id]
    await mysql.query(MYSQL.XIONGAN,[sql],arg)
    sheet[0].data = []
    ctx.body = sheet[0]
  }
}



//起始时间返回where sql语句的
function time_sql_where(start_time, end_time){
  let where_sql = "";
  if(start_time && end_time){
    where_sql = "where time_create>"+SetTimenum(start_time)+" and "+"time_create<"+SetTimenum(end_time); 
  }else if(start_time){
    where_sql = "where time_create>"+SetTimenum(start_time); 
  }else if(end_time){
    where_sql = "where time_create<"+SetTimenum(end_time); 
  }
  //转化时间为时间戳
  function SetTimenum(time){
    var timenum = Date.parse(new Date(time.replace(/\T/g,' '))).toString().slice(0,10);;
    return timenum;
  };  
  return where_sql;
}


// ---------------------------------------------------------------------------- DELETE
exports.delete = {
  
}